#include <stdio.h>
#include <error.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include <pthread.h>

#include "utility.h"
#include "log.h"
#include "chartSqliteOp.h"
#include "userSqliteOp.h"
#include <sys/types.h>


//创建聊天记录�?
const char *SQL_COMMAND_CREAT_CHART_TABLE = "CREATE TABLE %s ("
                                           "chartId UNSIGNED BIG INT    NOT NULL, " 	//会话id,类似QQ账号,不可更改
                                           "userId  UNSIGNED BIG INT    NOT NULL, " 	//用户id,类似QQ账号,不可更改
                                           "messageType UNSIGNED BIG INT NOT NULL, "    //消息类型
                                           "name            char(64)    NOT NULL, "     //发送人姓名
                                           "sendTime        DATETIME    NOT NULL, "     //消息发送时�?
                                           "content         char(512));";                //消息内�??

const char *SQL_COMMAND_CHART_INSERTTABLE = "INSERT INTO %s (chartId,userId,name,sendTime,messageType,content)"
                                           "VALUES(%llu, " //chartId,unsigned long long int
                                           "%llu, "        //userId,
                                           "'%s', "        //name
                                           "%s, "          //sendTime,发送时�?
                                           "%llu, "        //
                                           "'%s');";       //content
pthread_mutex_t sqlite3OpenMutex = PTHREAD_MUTEX_INITIALIZER;


/** 
 * insert1ChartRecord
 * @Descript:插入一条会话消�?
 * @Author	:hezuoqiang
 * @DateTime:2019�?11�?4�?T9:32:31+0800
 * @param	chartId	:
 * @param	userId	:
 * @param	name	:
 * @param	content	:
 * @RETURN VALUE 
 * @    On success, zero is returned.  On error, negative number is returned
 */
int insert1ChartRecord(u_int64_t chartId,u_int64_t userId, const char *name, const char *content)
{
    char *zErrMsg = 0;
    char sql[MAX_MESSAGE_SIZE];
	sqlite3 *db = NULL;
    int ret;
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);
    UTIL_ASSERT(db != NULL && name != NULL && content != NULL);

    bzero(sql, MAX_MESSAGE_SIZE);
    snprintf(sql, MAX_MESSAGE_SIZE, SQL_COMMAND_CHART_INSERTTABLE, CHART_TABLE_NAME, 
		(unsigned long long)chartId,(unsigned long long)userId, name, "strftime('%Y-%m-%d %H:%M:%f','now')", (unsigned long long)E_TEXT, content);
    ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "%s,SQL error: %s\n", __FUNCTION__,zErrMsg);
        sqlite3_free(zErrMsg);
	    my_sqlite3_close(db);
        return FAILED;
    }
	my_sqlite3_close(db);
    fprintf(stdout, "Records created successfully\n");
    return SUCCESS;
}
/** 
 * createAndJoinDialog
 * @Descript:创建并加入会�?
 * @Author	:hezuoqiang
 * @DateTime:2019�?11�?4�?T11:05:18+0800
 * @param	userId	:
 * @param	chartId	:
 * @RETURN VALUE 
 * @    On success, zero is returned.  On error, negative number is returned
 */
int createAndJoinDialog(u_int64_t userId,u_int64_t chartId)
{
    char *zErrMsg = 0;
    char sql[MAX_MESSAGE_SIZE];
	sqlite3 *db = NULL;
    int ret;

    /* 1.打开数据�? */
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);

    /* 2.构造sql�?�? */
    bzero(sql, MAX_MESSAGE_SIZE);
    //(chartId,userId,name,sendTime,messageType,content)
    snprintf(sql, MAX_MESSAGE_SIZE, SQL_COMMAND_CHART_INSERTTABLE, CHART_TABLE_NAME, 
		(unsigned long long)chartId,(unsigned long long)userId, "test", "strftime('%Y-%m-%d %H:%M:%f','now')", 
		(unsigned long long)E_SYSTEM, "I joined the dialog");
    ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "%s,SQL error: %s\n", __FUNCTION__,zErrMsg);
        sqlite3_free(zErrMsg);
	    my_sqlite3_close(db);
        return FAILED;
    }
    //INSERT INTO chart(chartId,userId,sendTime,messageType,content) VALUES(1,1,"2019-01-01"",1,"test");
    //update chart set name=(select name from user where id=22);
    snprintf(sql, MAX_MESSAGE_SIZE,"update %s set name=(select name from user where id=%llu) where messageType = %llu and userId=%llu;",
        CHART_TABLE_NAME,(unsigned long long)userId,(unsigned long long)E_SYSTEM,(unsigned long long)userId);
    ret = sqlite3_exec(db, sql, NULL, 0, &zErrMsg);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "%s,SQL error: %s\n", __FUNCTION__,zErrMsg);
        sqlite3_free(zErrMsg);
	    my_sqlite3_close(db);
        return FAILED;
    }
	my_sqlite3_close(db);
    fprintf(stdout, "Records created successfully\n");
    return SUCCESS;
}
/** 
 * getDialogList
 * @Descript:通过用户id获取会话列表
 * @Author	:hezuoqiang
 * @DateTime:2019�?11�?3�?T16:24:49+0800
 * @param	id	:
 * @param	resultNum	:
 * @RETURN VALUE 
 * @    On success, zero is returned.  On error, negative number is returned
 */
int getDialogList(u_int64_t id,u_int64_t  *resultNum,char **ppDialogList)
{
    int i = 0;
    int ret;
    int nRow;               /* Number of result rows written here */
    int nColumn;            /* Number of result columns written here */
    char *pzErrmsg = NULL;  /* Error msg written here */
    sqlite3 *db = NULL;
    char **dbResult;

    UTIL_ASSERT(resultNum !=NULL && ppDialogList!=NULL);

    /* 1.连接数据�? */
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);

    /* 2.构造查询条�? */
    char sqlcmd[MAX_BUF_SIZE];
    bzero(sqlcmd, MAX_BUF_SIZE);
    snprintf(sqlcmd, MAX_BUF_SIZE, "select chartId,strftime('%%s',sendTime) from %s where userId = %llu GROUP BY chartId ORDER BY sendTime;",CHART_TABLE_NAME, (unsigned long long)id);

    /* 3.获取结果 */
    ret = sqlite3_get_table(db,sqlcmd,&dbResult,&nRow,&nColumn,&pzErrmsg);
    if(ret != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table err:%s\n",pzErrmsg);
        if(pzErrmsg != NULL)
        {
            sqlite3_free(pzErrmsg);
            pzErrmsg = NULL;
        }
        sqlite3_free_table(dbResult);
        my_sqlite3_close(db);
        return -1;
    }
    printf("%s,%s:%d:nRow = %d,nColumn = %d\n",__FILE__,__FUNCTION__,__LINE__,nRow,nColumn);
    *resultNum = nRow;
    *ppDialogList = malloc(nRow * nColumn * sizeof(u_int64_t));
    UTIL_ASSERT(*ppDialogList !=NULL);
    for(i = 0; i < nRow;i++)
    {
        u_int64_t chartId = 0;
        u_int64_t createTime = 0;
        if(dbResult[(i+1)*nColumn +0])
        {
#if _BSD_SOURCE || _SVID_SOURCE || _XOPEN_SOURCE >= 600 || _ISOC99_SOURCE || _POSIX_C_SOURCE >= 200112L
            chartId = (u_int64_t)atoll(dbResult[(i+1)*nColumn +0]);
#else
            chartId = (u_int64_t)atol(dbResult[(i+1)*nColumn +0]);
#endif
        }
        if(dbResult[(i+1)*nColumn +1])
        {
#if _BSD_SOURCE || _SVID_SOURCE || _XOPEN_SOURCE >= 600 || _ISOC99_SOURCE || _POSIX_C_SOURCE >= 200112L
            createTime = (u_int64_t)atoll(dbResult[(i+1)*nColumn +1]);
#else
            createTime = (u_int64_t)atol(dbResult[(i+1)*nColumn +1]);
#endif
        }
        
        memcpy(*ppDialogList + (i * 2*sizeof(u_int64_t)), &chartId,sizeof(u_int64_t));
        memcpy(*ppDialogList + (i * 2*sizeof(u_int64_t))+sizeof(u_int64_t), &createTime,sizeof(u_int64_t));
        printf("%s,%s:%d:chartId = %llu,createTime = %llu\n",__FILE__,__FUNCTION__,__LINE__,(unsigned long long)chartId,(unsigned long long)createTime);
    }
    u_int64_t *p = (u_int64_t *)*ppDialogList;
    for(i=0;i<nRow*nColumn;i++)
    {
        printf("%d:%llu\n",i,(unsigned long long)p[i]);
    }
    sqlite3_free_table( dbResult );
    my_sqlite3_close(db);
    return 0;
}
/** 
 * getMessageList
 * @Descript:
 * @Author	:hezuoqiang
 * @DateTime:2019�?11�?4�?T20:45:16+0800
 * @param	id	:
 * @param	resultNum	:
 * @param	ppDialogList	:
 * @RETURN VALUE 
 * @    On success, zero is returned.  On error, negative number is returned
 */
int getHistoryMessage(u_int64_t id,u_int64_t  *resultNum,char **ppDialogList)
{
    int i = 0;
    int ret;
    int nRow;               /* Number of result rows written here */
    int nColumn;            /* Number of result columns written here */
    char *pzErrmsg = NULL;  /* Error msg written here */
    sqlite3 *db = NULL;
    char **dbResult;

    UTIL_ASSERT(resultNum !=NULL && ppDialogList!=NULL);

    /* 1.连接数据�? */
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);

    /* 2.构造查询条�? */
    char sqlcmd[MAX_BUF_SIZE];
    bzero(sqlcmd, MAX_BUF_SIZE);
    snprintf(sqlcmd, MAX_BUF_SIZE, 
"select userId,strftime('%%s',sendTime),messageType,user.name,content from %s INNER JOIN %s ON %s.userId=%s.id where chartId=%llu ORDER BY sendTime DESC LIMIT %u;",
     CHART_TABLE_NAME,USER_TABLE_NAME,CHART_TABLE_NAME,USER_TABLE_NAME, (unsigned long long)id,(signed int)LIMIT_HISTORY_NUM);
    //select userId,user.name,strftime('%s',sendTime),messageType,content from chart INNER JOIN user ON chart.userId=user.id where chartId=1;
    /* 3.获取结果 */
    ret = sqlite3_get_table(db,sqlcmd,&dbResult,&nRow,&nColumn,&pzErrmsg);
    if(ret != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table err:%s\n",pzErrmsg);
        if(pzErrmsg != NULL)
        {
            sqlite3_free(pzErrmsg);
            pzErrmsg = NULL;
        }
        sqlite3_free_table(dbResult);
        my_sqlite3_close(db);
        return -1;
    }
    *resultNum = nRow;
    *ppDialogList = malloc(nRow * (sizeof(u_int64_t)*3+NAME_LEN+CONTENT_LEN));
    UTIL_ASSERT(*ppDialogList !=NULL);
    for(i = 0; i < nRow;i++)
    {
        u_int64_t usertId = 0;
        u_int64_t sendTime = 0;
        u_int64_t messageType = 0;
        if(dbResult[(i+1)*nColumn +0])
        {
#if _BSD_SOURCE || _SVID_SOURCE || _XOPEN_SOURCE >= 600 || _ISOC99_SOURCE || _POSIX_C_SOURCE >= 200112L
            usertId = (u_int64_t)atoll(dbResult[(i+1)*nColumn +0]);
#else
            usertId = (u_int64_t)atol(dbResult[(i+1)*nColumn +0]);
#endif
        }
        if(dbResult[(i+1)*nColumn +1])
        {
#if _BSD_SOURCE || _SVID_SOURCE || _XOPEN_SOURCE >= 600 || _ISOC99_SOURCE || _POSIX_C_SOURCE >= 200112L
            sendTime = (u_int64_t)atoll(dbResult[(i+1)*nColumn +1]);
#else
            sendTime = (u_int64_t)atol(dbResult[(i+1)*nColumn +1]);
#endif
        }
        if(dbResult[(i+1)*nColumn +2])
        {
            messageType = (u_int64_t)atoll(dbResult[(i+1)*nColumn +2]);
        }
        char *p = *ppDialogList + (i * (sizeof(u_int64_t)*3+NAME_LEN+CONTENT_LEN));
        memcpy(p,&usertId,sizeof(u_int64_t));
        memcpy(p+sizeof(u_int64_t),&sendTime,sizeof(u_int64_t));
        memcpy(p+sizeof(u_int64_t)*2,&messageType,sizeof(u_int64_t));
        if(dbResult[(i+1)*nColumn +3])
            memcpy(p+sizeof(u_int64_t)*3, dbResult[(i+1)*nColumn +3],NAME_LEN);
        if(dbResult[(i+1)*nColumn +4])
            memcpy(p+sizeof(u_int64_t)*3+NAME_LEN, dbResult[(i+1)*nColumn +4],CONTENT_LEN);
        //printf("%s,%s:%d:usertId = %llu,sendTime = %llu,messageType = %llu\n",__FILE__,__FUNCTION__,__LINE__,
        //    (unsigned long long)usertId,(unsigned long long)sendTime,(unsigned long long)messageType);
    }
    sqlite3_free_table( dbResult );
    my_sqlite3_close(db);
    return 0;
}
/** 
 * joinInDialog
 * @Descript:加入会话
 * @Author	:hezuoqiang
 * @DateTime:2019�?11�?4�?T10:23:45+0800
 * @param	userId	:
 * @param	chartId	:
 * @RETURN VALUE 
 * @    On success, zero is returned.  On error, negative number is returned
 */
int joinInDialog(u_int64_t userId,u_int64_t chartId)
{
    //int i = 0;
    int ret;
    int nRow;               /* Number of result rows written here */
    int nColumn;            /* Number of result columns written here */
    char *pzErrmsg = NULL;  /* Error msg written here */
    sqlite3 *db = NULL;
    char **dbResult;
    
    /* 1.连接数据�? */
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);

    /* 2.构造查询条�? */
    char sqlcmd[MAX_BUF_SIZE];
    bzero(sqlcmd, MAX_BUF_SIZE);
    snprintf(sqlcmd, MAX_BUF_SIZE, "select chartId from %s where chartId=%llu and userId = %llu and messageType = %llu GROUP BY chartId",
        CHART_TABLE_NAME, (unsigned long long)chartId, (unsigned long long)userId,(unsigned long long)E_SYSTEM);
    puts(sqlcmd);

    /* 3.查�?�是否存在这�?会话id */
    ret = sqlite3_get_table(db,sqlcmd,&dbResult,&nRow,&nColumn,&pzErrmsg);
    if(ret != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table err:%s\n",pzErrmsg);
        if(pzErrmsg != NULL)
        {
            sqlite3_free(pzErrmsg);
            pzErrmsg = NULL;
        }
        sqlite3_free_table(dbResult);
        my_sqlite3_close(db);
        return -1;
    }
    //printf("%s,%s:%d:nRow = %d,nColumn = %d\n",__FILE__,__FUNCTION__,__LINE__,nRow,nColumn);
    if(nRow == 0 || nColumn == 0 || dbResult[1*nColumn +0] == NULL)
    {
        //printf("会话id不存�?,现在创建\n");
        my_sqlite3_close(db);
        sqlite3_free_table( dbResult );
        ret = createAndJoinDialog(userId,chartId);
        add_log("用户id '%llu' 创建了会话id '%llu'\n",(unsigned long long)userId,(unsigned long long)chartId);
        UTIL_ASSERT(ret == SUCCESS);
        return 0;
    }

    sqlite3_free_table( dbResult );
    my_sqlite3_close(db);
    return 0;
}

/** 
 * getDialogPersonList
 * @Descript:根据会话id获取参与人员
 * @Author	:hezuoqiang
 * @DateTime:2019�?11�?4�?T9:59:41+0800
 * @param	id	:
 * @param	resultNum	:
 * @param	ppDialogList	:
 * @RETURN VALUE 
 * @    On success, zero is returned.  On error, negative number is returned
 */
int getDialogPersonList(u_int64_t id,u_int64_t  *resultNum,char **ppPersonList)
{
    int i = 0;
    int ret;
    int nRow;               /* Number of result rows written here */
    int nColumn;            /* Number of result columns written here */
    char *pzErrmsg = NULL;  /* Error msg written here */
    sqlite3 *db = NULL;
    char **dbResult;

    UTIL_ASSERT(resultNum !=NULL && ppPersonList!=NULL);

    /* 1.连接数据�? */
    ret = my_sqlite3_open(DB_NAME, &db);
    UTIL_ASSERT(!ret);

    /* 2.构造查询条�? */
    char sqlcmd[MAX_BUF_SIZE];
    bzero(sqlcmd, MAX_BUF_SIZE);
    snprintf(sqlcmd, MAX_BUF_SIZE, "select userId,strftime('%%s',sendTime),user.name from %s INNER JOIN %s ON %s.userId=%s.id where "
        "chartId=%llu and messageType=%llu ORDER BY sendTime;",
        CHART_TABLE_NAME,USER_TABLE_NAME,CHART_TABLE_NAME,USER_TABLE_NAME,(unsigned long long)id,(unsigned long long)E_SYSTEM);
    puts(sqlcmd);
    //select chart.userId,strftime('%s',chart.sendTime),user.name from chart INNER JOIN user ON user.id=chart.userId where chartId=1 and messageType=0;
    /* 3.获取结果 */
    ret = sqlite3_get_table(db,sqlcmd,&dbResult,&nRow,&nColumn,&pzErrmsg);
    if(ret != SQLITE_OK)
    {
        fprintf(stderr,"sqlite3_get_table err:%s\n",pzErrmsg);
        if(pzErrmsg != NULL)
        {
            sqlite3_free(pzErrmsg);
            pzErrmsg = NULL;
        }
        sqlite3_free_table(dbResult);
        my_sqlite3_close(db);
        return -1;
    }
    //printf("%s,%s:%d:nRow = %d,nColumn = %d\n",__FILE__,__FUNCTION__,__LINE__,nRow,nColumn);
    *resultNum = nRow;
    *ppPersonList = malloc(nRow * (sizeof(u_int64_t)*2+NAME_LEN));
    UTIL_ASSERT(*ppPersonList != NULL);
    bzero(*ppPersonList,nRow * (sizeof(u_int64_t)*2+NAME_LEN));
    char *p=NULL;
    for(i = 0; i < nRow;i++)
    {
        p = *ppPersonList + i * (2*sizeof(u_int64_t)+NAME_LEN);
        u_int64_t userId = 0;
        u_int64_t createTime = 0;
        if(dbResult[(i+1)*nColumn +0])
        {
#if _BSD_SOURCE || _SVID_SOURCE || _XOPEN_SOURCE >= 600 || _ISOC99_SOURCE || _POSIX_C_SOURCE >= 200112L
            userId = (u_int64_t)atoll(dbResult[(i+1)*nColumn +0]);
#else
            userId = (u_int64_t)atol(dbResult[(i+1)*nColumn +0]);
#endif
        }
        if(dbResult[(i+1)*nColumn +1])
        {
#if _BSD_SOURCE || _SVID_SOURCE || _XOPEN_SOURCE >= 600 || _ISOC99_SOURCE || _POSIX_C_SOURCE >= 200112L
            createTime = (u_int64_t)atoll(dbResult[(i+1)*nColumn +1]);
#else
            createTime = (u_int64_t)atol(dbResult[(i+1)*nColumn +1]);
#endif
        }
        memcpy(p, &userId,sizeof(u_int64_t));
        memcpy(p+sizeof(u_int64_t), &createTime,sizeof(u_int64_t));
        memcpy(p+sizeof(u_int64_t)*2,dbResult[(i+1)*nColumn +2],
            MIN(strlen(dbResult[(i+1)*nColumn +2]),NAME_LEN));
    }
    sqlite3_free_table( dbResult );
    my_sqlite3_close(db);
    return 0;
}



